package dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Set;

import connection.ConnectionManager;
import domain.Event;
public class JDBCEventDAO implements IEventDAO {

	Connection conn;
	
	public List<Event> getAllEvents() {
		List<Event> events = new LinkedList<Event>();
		Map<String, String> guests= new HashMap<String, String>();
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT e.ideventos, u.login, e.nombre, e.descripcion, e.fechaEvento " +
					"FROM eventos e INNER JOIN usuarios u ON u.uid = e.usuarios_uid");
			Event event = null;
			while(rs.next()){
				guests=this.getAllGuestsByEvent(rs.getInt(1));
				event = new Event(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), guests,utils.util.ConvertDateToString(rs.getObject(5)));
				events.add(event);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return events;
	}
	
	public List<Event> getAllEventsByUser(String loginUser) {
		List<Event> events = new LinkedList<Event>();
		Map<String, String> guests= new HashMap<String, String>();
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT e.ideventos, u.login, e.nombre, e.descripcion, e.fechaEvento " +
					"FROM eventos e INNER JOIN usuarios u ON u.uid = e.usuarios_uid WHERE u.login='"+loginUser+"';");
			Event event = null;
			while(rs.next()){
				guests=this.getAllGuestsByEvent(rs.getInt(1));
				event = new Event(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), guests,utils.util.ConvertDateToString(rs.getObject(5)));
				events.add(event);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return events;
	}
	
	public List<Event> getAllEventsByName(String name) {
		List<Event> events = new LinkedList<Event>();
		Map<String, String> guests= new HashMap<String, String>();
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT e.ideventos, u.login, e.nombre, e.descripcion, e.fechaEvento " +
					"FROM eventos e INNER JOIN usuarios u ON u.uid = e.usuarios_uid WHERE e.nombre='"+name+"';");
			Event event = null;
			while(rs.next()){
				guests=this.getAllGuestsByEvent(rs.getInt(1));
				event = new Event(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), guests, utils.util.ConvertDateToString(rs.getObject(5)));
				events.add(event);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return events;
	}
	
	public Event getEventById(Integer idEvent) {
		Event event = null;
		Map<String, String> guests= new HashMap<String, String>();
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT e.ideventos, u.login, e.nombre, e.descripcion, e.fechaEvento " +
					"FROM eventos e INNER JOIN usuarios u ON u.uid = e.usuarios_uid WHERE e.ideventos = " + idEvent + ";");
			if(rs.next()){
				guests=this.getAllGuestsByEvent(rs.getInt(1));
				event = new Event(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), guests,utils.util.ConvertDateToString(rs.getObject(5)));
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return event;
	}
	
	public Map<String, String> getAllGuestsByEvent(Integer idEvent){
		Map<String, String> guests= new HashMap<String, String>();
		try{
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT u.login, a.asistencia " +
					"FROM asistencia_eventos a INNER JOIN usuarios u ON u.uid = a.usuarios_uid WHERE a.eventos_ideventos="+idEvent+";");
			while(rs.next()){
				guests.put(rs.getString(1), rs.getString(2));
			}
			rs.close();
			stmt.close();
		}catch (Exception e) {
			e.printStackTrace();
		}
		return guests;
	}
	
	/**
	 * create new event
	 */
	public void createEvent(Event newEvent){
		Integer userID = null;
		Integer eventID = null;
		try{
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM usuarios WHERE login = '" + newEvent.getLoginUser() +"'");
			if(rs.next()){
				userID = rs.getInt(1);
			}
			stmt.executeUpdate("INSERT INTO eventos (usuarios_uid, nombre, descripcion, fechaEvento) VALUES ("+userID+ ",'" +newEvent.getName() +"','" +newEvent.getDescription()+"','"+newEvent.getEventDate()+"')" );
			Set<String> invitedUsers = newEvent.getGuests().keySet();
			rs = stmt.executeQuery("select ideventos from eventos order by ideventos DESC limit 1");
			if(rs.next()){
				eventID = rs.getInt(1);
			}
			for(String login : invitedUsers){
				rs = stmt.executeQuery("SELECT * FROM usuarios WHERE login = '" + login +"'");
				if(rs.next()){
					userID = rs.getInt(1);
				}
				stmt.executeUpdate("INSERT INTO asistencia_eventos (usuarios_uid, eventos_ideventos, asistencia)  VALUES ("+userID+ "," +eventID+",'"+ newEvent.getGuests().get(login) + "')" );
			}
			rs.close();
			stmt.close();
		}catch (Exception e) {
			e.printStackTrace();
		}
	}

	@Override
	public Integer getLastCreatedEvent() {
		Integer idvideo = null;
		try{
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT ideventos FROM eventos ORDER BY ideventos DESC LIMIT 1;");
			while(rs.next()){
				idvideo = rs.getInt(1);
			}
			rs.close();
			stmt.close();
		}catch (Exception e) {
			e.printStackTrace();
		}
		return idvideo;
	}
	
	public Integer getNumberEventsToUser(Integer idUser) {
		Integer eventos = null;
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			stmt.executeUpdate("DELETE FROM eventos WHERE fechaEvento < SYSDATE()");
			ResultSet rs = stmt.executeQuery("SELECT count(*) FROM asistencia_eventos WHERE usuarios_uid="+idUser);
			if(rs.next())
			{
				eventos= rs.getInt(1);				
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return eventos;
	}
	
	public List<Integer> getEventsWithInvitedUser(Integer idUser) {
		List<Integer> events = new LinkedList<Integer>();
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT eventos_ideventos FROM asistencia_eventos WHERE usuarios_uid="+idUser+";");
			while(rs.next()){
				events.add(rs.getInt(1));				
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return events;
	}

	@Override
	public void updateAssistance(String assistance, Integer idEvent, Integer idUser) {
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			stmt.execute("UPDATE asistencia_eventos SET asistencia = '"+assistance
					+ "' WHERE usuarios_uid = "+  idUser + " AND eventos_ideventos = "+ idEvent);
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}
}
